/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Helper.BillsDetailHelper;
import Helper.CategoryHelper;
import Helper.ItemHelper;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author KIENLT
 */
public class ItemDAO {
    public static int Insert(Connection cnn, ItemHelper obj) throws Exception{
        String strSQL = "INSERT INTO tblItems(IName, Price, QuantityAvailable, Manufacturer, Description, CatID, DateImport) VALUES(?,?,?,?,?,?,GETDATE())";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getName());
        stmt.setString(2, obj.getPrice());
        stmt.setString(3, obj.getQuantity());
        stmt.setString(4, obj.getManufacturer());
        stmt.setString(5, obj.getDescription());
        stmt.setString(6, obj.getCategoryId());
        return stmt.executeUpdate();
    }
    
    public static int Update(Connection cnn, ItemHelper obj) throws Exception{
        String strSQL = "UPDATE tblItems SET IName=?, Price=?, QuantityAvailable=?, Manufacturer=?, Description=?, CatID=? WHERE IId=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getName());
        stmt.setString(2, obj.getPrice());
        stmt.setString(3, obj.getQuantity());
        stmt.setString(4, obj.getManufacturer());
        stmt.setString(5, obj.getDescription());
        stmt.setString(6, obj.getCategoryId());
        stmt.setString(7, obj.getId());
        return stmt.executeUpdate();
    }
    
    public static int UpdateStatus(Connection cnn, ItemHelper obj) throws Exception{
        String status = obj.getStatus().equals("0") ? "1" : "0";
        String strSQL = "UPDATE tblItems SET Status=? WHERE IId=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, status);
        stmt.setString(2, obj.getId());
        return stmt.executeUpdate();
    }
    public static int UpdateStatusend(Connection cnn, ItemHelper obj) throws Exception{
        String strSQL = "UPDATE tblItems SET Status='1' WHERE IId=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeUpdate();
    }
    
    public static int UpdateQuantity(Connection cnn, ItemHelper obj) throws Exception{
        String strSQL = "UPDATE tblItems SET QuantityAvailable=? WHERE IId=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getQuantity());
        stmt.setString(2, obj.getId());
        return stmt.executeUpdate();
    }
    
    public static int Delete(Connection cnn, ItemHelper obj) throws Exception{
        String strSQL = "DELETE FROM tblItems WHERE IId = ?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeUpdate();
    }
    
   public static ResultSet FindOne(Connection cnn, ItemHelper obj) throws Exception {
        String strSQL = "SELECT * FROM tblItems t1, tblCategories t2 WHERE t1.CatID=t2.CatID and t1.IId=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeQuery();
    }
   
   public static ResultSet FindQuantity(Connection cnn, String itemId) throws Exception{
        String strSQL = "SELECT * FROM tblItems WHERE IId=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, itemId);
        return stmt.executeQuery();
   }
   
    public static ArrayList<ItemHelper> FindAll(Connection cnn) throws Exception{
        ArrayList<ItemHelper> arrItemList = new ArrayList<ItemHelper>();
        ItemHelper itemData = null;
        String strSQL = "SELECT * FROM tblItems t1, tblCategories t2 where t1.CatID=t2.CatID and t1.Status=1";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            itemData = new ItemHelper();
            itemData.setId(rs.getString("IId"));
            itemData.setName(rs.getString("IName"));
            itemData.setPrice(rs.getString("Price"));
            itemData.setQuantity(rs.getString("QuantityAvailable"));
            itemData.setCategoryName(rs.getString("CatName"));
            arrItemList.add(itemData);
        }
        return arrItemList;
    }
    public static ArrayList<ItemHelper> FindAllEnd(Connection cnn) throws Exception{
        ArrayList<ItemHelper> arrItemList = new ArrayList<ItemHelper>();
        ItemHelper itemData = null;
        String strSQL = "SELECT * FROM tblItems t1, tblCategories t2 where t1.CatID=t2.CatID and t1.Status=0";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            itemData = new ItemHelper();
            itemData.setId(rs.getString("IId"));
            itemData.setName(rs.getString("IName"));
            itemData.setPrice(rs.getString("Price"));
            itemData.setQuantity(rs.getString("QuantityAvailable"));
            itemData.setCategoryName(rs.getString("CatName"));
            arrItemList.add(itemData);
        }
        return arrItemList;
    }
 
    public static ArrayList<ItemHelper> FindOne(Connection cnn, ArrayList obj) throws Exception{
        ArrayList<ItemHelper> arrItemList = new ArrayList<ItemHelper>();
        ItemHelper itemData = null;
        String strSQL = "SELECT * FROM tblItems t1, tblCategories t2 where t1.CatID=t2.CatID ";
        if(obj.isEmpty()){
            strSQL += " and t1.IID=0";
        }
        if(obj.size() == 1)
            strSQL += " and t1.IID="+((BillsDetailHelper)obj.get(0)).getId();
        else if(obj.size() > 1){
            strSQL += " and (";
            for(int i=0; i<obj.size(); i++){
                if(i==0)
                    strSQL += " t1.IID="+((BillsDetailHelper)obj.get(i)).getId();
                else
                    strSQL += " or t1.IID="+((BillsDetailHelper)obj.get(i)).getId();
            }
            strSQL += " )";
        }
        
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        ResultSet rs = stmt.executeQuery();
        int i = 0;
        while(rs.next()){
            itemData = new ItemHelper();
            itemData.setId(rs.getString("IId"));
            itemData.setName(rs.getString("IName"));
            itemData.setPrice(rs.getString("Price"));
            itemData.setQuantity(((BillsDetailHelper)obj.get(i)).getQuantiry());
            itemData.setCategoryName(rs.getString("CatName"));
            arrItemList.add(itemData);
            i++;
        }
        return arrItemList;
    }
    
    public static ArrayList<ItemHelper> FindByCategory(Connection cnn, CategoryHelper obj) throws Exception{
        ArrayList<ItemHelper> arrItemList = new ArrayList<ItemHelper>();
        ItemHelper itemData = null;
        String strSQL = "SELECT * FROM tblItems t1, tblCategories t2 where t1.CatID=t2.CatID and t1.CatID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            itemData = new ItemHelper();
            itemData.setId(rs.getString("IId"));
            itemData.setName(rs.getString("IName"));
            itemData.setPrice(rs.getString("Price"));
            itemData.setQuantity(rs.getString("QuantityAvailable"));
            itemData.setCategoryName(rs.getString("CatName"));
            arrItemList.add(itemData);
        }
        return arrItemList;
    }
    
    public static ArrayList<ItemHelper> Searchend(Connection cnn, ItemHelper obj) throws Exception{
        ArrayList<ItemHelper> arrUserList = new ArrayList<ItemHelper>();
        String strSQL = "SELECT * FROM tblItems t1, tblCategories t2 where t1.CatID=t2.CatID and t1.Status='0'";
        if(!obj.getName().equals("")){
            strSQL += " and t1.IName like '%"+obj.getName()+"%'";
        }
        if(!obj.getCategoryId().equals("")){
            strSQL += " and t1.CatID = '"+obj.getCategoryId()+"'";
        }
        
        Statement stmt = cnn.createStatement();
        ResultSet rs = stmt.executeQuery(strSQL);
        while(rs.next()){
            obj = new ItemHelper();
            obj.setId(rs.getString("IID"));
            obj.setName(rs.getString("IName"));
            obj.setCategoryName(rs.getString("CatName"));
            obj.setDescription(rs.getString("Description"));
            obj.setPrice(rs.getString("Price"));
            obj.setQuantity(rs.getString("QuantityAvailable"));
            arrUserList.add(obj);
        }
        return arrUserList;
    }
     public static ArrayList<ItemHelper> Search(Connection cnn, ItemHelper obj) throws Exception{
        ArrayList<ItemHelper> arrUserList = new ArrayList<ItemHelper>();
        String strSQL = "SELECT * FROM tblItems t1, tblCategories t2 where t1.CatID=t2.CatID and t1.Status='1'";
        if(!obj.getName().equals("")){
            strSQL += " and t1.IName like '%"+obj.getName()+"%'";
        }
        if(!obj.getCategoryId().equals("")){
            strSQL += " and t1.CatID = '"+obj.getCategoryId()+"'";
        }
        
        Statement stmt = cnn.createStatement();
        ResultSet rs = stmt.executeQuery(strSQL);
        while(rs.next()){
            obj = new ItemHelper();
            obj.setId(rs.getString("IID"));
            obj.setName(rs.getString("IName"));
            obj.setCategoryName(rs.getString("CatName"));
            obj.setDescription(rs.getString("Description"));
            obj.setPrice(rs.getString("Price"));
            obj.setQuantity(rs.getString("QuantityAvailable"));
            arrUserList.add(obj);
        }
        return arrUserList;
    }
}
